﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gpsys.Entity.MSG;
using Gpsys.Logger;

namespace Gpsys.Web.Business
{
    public class UserBusiness : BaseBusiness
    {
        public bool InsertUser(User user)
        {
            try
            {
                SQL.OpenConnection();

                user.Id = SQL.Query<int>(@"INSERT INTO [User]
                                           ([UserName]
                                           ,[Name]
                                           ,[Description]
                                           ,[CreatedOn]
                                           ,[LastLogin]
                                           ,[IsDeleted])
                                     VALUES
                                           (@userName,
                                            @name,
                                            @description,
                                            getdate(),
                                            NULL,
                                            0)

                                     SELECT CAST(SCOPE_IDENTITY() as int);",
                                                         new
                                                         {
                                                             userName = user.UserName,
                                                             name = user.Name,
                                                             description = user.Description,
                                                         }).SingleOrDefault();

            }
            catch (Exception e)
            {
                Log.WriteError("InsertUser : " + e.Message);

                return false;
            }
            finally
            {
                SQL.CloseConnection();
            }

            return true;
        }

        public bool UpdateUser(User user)
        {
            try
            {
                SQL.OpenConnection();

                SQL.Execute(@"UPDATE [User]
                            SET [Name] = @name,
                                [Description] = @description,
                                [LastLogin] = @lastLogin
                            WHERE [Id] = @Id",
                                        new
                                        {
                                            Id = user.Id,
                                            name = user.Name,
                                            description = user.Description,
                                            lastLogin = user.LastLogin
                                        });

            }
            catch (Exception e)
            {
                Log.WriteError("UpdateUser : " + e.Message);

                return false;
            }
            finally
            {
                SQL.CloseConnection();
            }

            return true;
        }

        public bool DeleteUser(int Id)
        {
            try
            {
                SQL.OpenConnection();

                SQL.Execute(@"UPDATE [User]
                                               SET IsDeleted =  @IsDeleted
                                             WHERE [Id] = @Id",
                                                         new
                                                         {
                                                             Id = Id,
                                                             IsDeleted = true
                                                         });

            }
            catch (Exception e)
            {
                Log.WriteError("DeleteUser : " + e.Message);

                return false;
            }
            finally
            {
                SQL.CloseConnection();
            }

            return true;
        }

        public List<User> GetUsers()
        {
            try
            {
                SQL.OpenConnection();

                return SQL.Query<User>(@"SELECT [Id]
                                          ,[UserName]
                                          ,[Name]
                                          ,[Description]
                                          ,[CreatedOn]
                                          ,[LastLogin]
                                          ,[IsDeleted]
                                         FROM [User]
                                        Where IsDeleted = 0").ToList();

            }
            catch (Exception e)
            {
                Log.WriteError("GetUsers : " + e.Message);

                return new List<User>();
            }
            finally
            {
                SQL.CloseConnection();
            }
        }

        public User GetUser(int Id)
        {
            try
            {
                SQL.OpenConnection();

                return SQL.Query<User>(@"SELECT [Id]
                                              ,[UserName]
                                              ,[Name]
                                              ,[Description]
                                              ,[CreatedOn]
                                              ,[LastLogin]
                                              ,[IsDeleted]
                                          FROM [User]
                                          WHERE [Id] = @id", new { id = Id }).SingleOrDefault();

            }
            catch (Exception e)
            {
                Log.WriteError("GetUser : " + e.Message);

                return null;
            }
            finally
            {
                SQL.CloseConnection();
            }
        }

        public User GetUser(string userName)
        {
            try
            {
                SQL.OpenConnection();

                return SQL.Query<User>(@"SELECT [Id]
                                              ,[UserName]
                                              ,[Name]
                                              ,[Description]
                                              ,[CreatedOn]
                                              ,[LastLogin]
                                              ,[IsDeleted]
                                          FROM [User]
                                          WHERE Upper([UserName]) = @UserName", new { UserName = userName.ToUpper() }).SingleOrDefault();

            }
            catch (Exception e)
            {
                Log.WriteError("GetUser : " + e.Message);

                return null;
            }
            finally
            {
                SQL.CloseConnection();
            }
        }

        public bool CheckCodeUsed(User user)
        {
            try
            {
                SQL.OpenConnection();

                return SQL.Query<int>(@"Select count(1) from [User] where Upper([UserName]) = @userName and [Id] != @Id",
                                                         new
                                                         {
                                                             Id = user.Id,
                                                             userName = user.UserName.ToUpper()
                                                         }).SingleOrDefault() > 0;

            }
            catch (Exception e)
            {
                Log.WriteError("CheckCodeUsed : " + e.Message);

                return false;
            }
            finally
            {
                SQL.CloseConnection();
            }
        }
    }
}
